package fr.web325.dao;

import fr.web325.utils.QueryRunnerUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class SalesDao {


    // 获取销售榜单数据
    public List<Map<String,Object>> saleList(String year, String month) throws SQLException {
        // 获取数据库连接池对象
        QueryRunner qr = new QueryRunner(QueryRunnerUtils.getDateSource());
        String sql = "select product_id,products.name,sum(buynum) totalsnum from orderitem,products,orders" +
                " where orderitem.product_id = products.id " +
                " and orderitem.order_id = orders.id" +
                " and orders.paystate = 1" +
                " and year(orders.ordertime)=?" +
                " and month(orders.ordertime)=?" +
                " group by product_id order by totalsnum desc";
        return qr.query(sql,new MapListHandler(),year,month);
    }
}
